package com.jvxb.manage.livable.service.dbQuery.impl;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jvxb.common.constants.DbConstants;
import com.jvxb.common.utils.CommonUtil;
import com.jvxb.manage.livable.service.AssetsInstanceService;
import com.jvxb.manage.livable.service.dbQuery.MysqlQueryService;
import com.jvxb.manage.livable.util.JdbcUtil;
import com.jvxb.manage.livable.util.RedisUtil;
import com.jvxb.manage.livable.util.SqlCheckUtil;
import com.jvxb.manage.livable.vo.MySQLQueryVo;
import com.jvxb.modules.configuration.security.SecurityUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author jvxb
 * @since 2022-02-08
 */
@Service
public class MysqlQueryServiceImpl implements MysqlQueryService {

    private static final int MAXPAGE = 1000;
    private static final int MINPAGE = 1;
    private static final int MAXPAGESIZE = 200;

    @Autowired
    AssetsInstanceService assetsInstanceService;

    @Override
    public List<String> getAllSql(String sqls) {
        List<SQLStatement> sqlStatements;
        try {
            sqlStatements = SQLUtils.parseStatements(sqls, DbConstants.MYSQL.toLowerCase());
        } catch (Exception e) {
            throw new RuntimeException("SQL解析异常，请检查：" + e.getMessage());
        }
        if (sqlStatements != null && sqlStatements.size() > 5) {
            throw new RuntimeException("最多同时查询5条，请重新修改");
        }
        return sqlStatements.stream().map(e -> e.toString()).collect(Collectors.toList());
    }

    @Override
    public String formatSql(String sqls) {
        List<SQLStatement> sqlStatements;
        try {
            sqlStatements = SQLUtils.parseStatements(sqls, DbConstants.MYSQL.toLowerCase());
        } catch (Exception e) {
            throw new RuntimeException("SQL解析异常，请检查：" + e.getMessage());
        }
        if (sqlStatements != null && sqlStatements.size() > 1) {
            throw new RuntimeException("格式化功能不支持多条SQL同时执行");
        }
        return sqlStatements.get(0).toString();
    }

    @Override
    public List<String> getDatabase(MySQLQueryVo mySQLQueryVo) {
        try {
            List<String> query = new ArrayList<>();
            String sql = "show databases";
            List<String> databases = JdbcUtil.queryListString(mySQLQueryVo.getHost(), mySQLQueryVo.getPort(), sql);
            for (String db : databases) {
                if (!db.matches("information_schema|performance_schema|mysql|sys|test")) {
                    query.add(db);
                }
            }
            return query;
        } catch (Exception e) {
            throw new RuntimeException("查询异常：" + e.getMessage());
        }
    }

    @Override
    public List<String> getTable(MySQLQueryVo mySQLQueryVo) {
        try {
            String sql = String.format("select distinct TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '%s'", mySQLQueryVo.getDbName());
            List<String> tables = JdbcUtil.queryListString(mySQLQueryVo.getHost(), mySQLQueryVo.getPort(), sql);
            return tables;
        } catch (Exception e) {
            throw new RuntimeException("查询异常：" + e.getMessage());
        }
    }

    @Override
    public String showCreateTable(MySQLQueryVo mySQLQueryVo) {
        try {
            String sql = String.format("show create table `%s`.`%s`", mySQLQueryVo.getDbName(), mySQLQueryVo.getTableName());
            Map<String, Object> result = JdbcUtil.queryOne(mySQLQueryVo.getHost(), mySQLQueryVo.getPort(), sql);
            return result.getOrDefault("Create Table", "").toString();
        } catch (Exception e) {
            throw new RuntimeException("查询异常：" + e.getMessage());
        }
    }

    @Override
    public Map mysqlQuery(MySQLQueryVo mySQLQueryVo) {
        Connection connection = null;
        try {
            //1）检查是否允许执行
            sqlExecuteCheck(mySQLQueryVo);
            //2）查询总数及设置分页
            String totolSql = String.format("select count(1) from (%s) t", mySQLQueryVo.getSqls());
            String limitSql = SqlCheckUtil.addLimit(mySQLQueryVo.getSqls(), mySQLQueryVo.getCurrentPage(), mySQLQueryVo.getPageSize());
            long startTime = System.currentTimeMillis();
            //3）查询结果
            connection = JdbcUtil.getConnection(mySQLQueryVo.getHost(), mySQLQueryVo.getPort(), mySQLQueryVo.getDbName());
            List<Map<String, Object>> totalRowList = JdbcUtil.queryList(connection, totolSql);
            String totalRow = totalRowList.get(0).get("count(1)").toString();
            List<Map<String, Object>> dataList = Collections.EMPTY_LIST;
            if (totalRow != null && !totalRow.equalsIgnoreCase("0")) {
                dataList = JdbcUtil.queryList(connection, limitSql);
            }
            long endTime = System.currentTimeMillis();
            Map dataMap = new HashMap();
            dataMap.put("useTime(ms)", endTime - startTime);
            dataMap.put("totalRow", Long.valueOf(totalRow));
            dataMap.put("data", dataList);
            //4）保存查询记录
            saveQueryRecord(mySQLQueryVo);
            return dataMap;
        } catch (Exception e) {
            throw new RuntimeException("查询异常：" + e.getMessage());
        } finally {
            JdbcUtil.close(connection);
        }
    }

    @Override
    public void getMysqlQueryRecord(Long clusterId, IPage page) {
        Long userId = SecurityUtil.currentUserId();
        String queryRecordKey = "query:mysql:" + userId + ":" + clusterId;
        int begin = (int) ((page.getCurrent() - 1) * page.getSize());
        int end = (int) (begin + page.getSize() - 1);
        Set set = RedisUtil.zReverseRange(queryRecordKey, begin, end);
        page.setRecords(new ArrayList(set));
        page.setTotal(RedisUtil.zcard(queryRecordKey));
    }

    private void saveQueryRecord(MySQLQueryVo mySQLQueryVo) {
        //格式化sql
        String format = SQLUtils.formatHive(mySQLQueryVo.getSqls());
        String formatSql = format.replace("\n", " ");
        String historySQL = JSON.toJSONString(CommonUtil.getMap("sql", formatSql, "dbName", mySQLQueryVo.getDbName()));
        Long userId = SecurityUtil.currentUserId();
        String queryRecordKey = "query:mysql:" + userId + ":" + mySQLQueryVo.getClusterId();
        RedisUtil.zSet(queryRecordKey, historySQL, System.currentTimeMillis());
        //保存50条记录，保存30天
        if (RedisUtil.zcard(queryRecordKey) > 50) {
            RedisUtil.zRemoveRange(queryRecordKey, 50, -1);
        }
        RedisUtil.expire(queryRecordKey, 30 * 24 * 60 * 60);
    }

    private void sqlExecuteCheck(MySQLQueryVo mySQLQueryVo) throws Exception {
        //检查sql语句
        if (!(SQLUtils.parseStatements(mySQLQueryVo.getSqls(), DbConstants.MYSQL.toLowerCase()).get(0) instanceof SQLSelectStatement)) {
            throw new RuntimeException("仅支持查询语句，若须变更请使用[数据变更]功能。");
        }
        //检查分页
        if (mySQLQueryVo.getPageSize() > MAXPAGESIZE) {
            throw new RuntimeException("超过单页最大限制：" + MAXPAGESIZE);
        }
        if (mySQLQueryVo.getCurrentPage() > MAXPAGE || mySQLQueryVo.getCurrentPage() < MINPAGE) {
            throw new RuntimeException("最大不超过" + MAXPAGE + "页");
        }
        // 其他检测...
    }
}
